<!DOCTYPE html><html class="appearance-auto" lang="zh-CN"><head><meta charset="UTF-8"><title>【手撕SQL】SQLite UPDATE 命令执行计划</title><meta name="description" content="用创业者心态做好每一件事情！"><meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no, initial-scale=1"><!-- Google Analytics --><!-- End Google Analytics -->
<!-- Baidu Analytics --><!-- End Baidu Analytics --><link rel="icon" href="/blog/images/favicon.png"><link rel="stylesheet" href="/blog/style/common/bulma.css"><link rel="stylesheet" href="/blog/style/base.css"><link rel="stylesheet" href="/blog/style/common/helper.css"><script src="/blog/js/common.js"></script><link rel="stylesheet" href="/blog/style/post.css"><link rel="stylesheet" href="/blog/style/themes/highlight-theme-light.css"><script src="/blog/js/highlight.pack.js"></script><meta name="description" content="
本文主要讲述SQLite通过EXPLAIN命令获取UPDATE语句的执行计划。分析UPDATE执行过程中对表中各个属性的操作过程。


一、问题抛出表结构如下：

data属性字段的类型为text，在实际中data字段保存着bigdata（测试过程时候发现有24M）。写作本文的目的在于分析。通过下面update语句，SQLite在执行过程中是否会把data属性字段里面的数据读入到内存中。
update cache_object set key1 = &amp;#39;theme_1&amp;#39; where key1 = &amp;#39;theme&amp;#39;;

二、分析步骤1、先分析一条SELECT语句：通过key1=‘theme’去查询对应元组里面的属性key2
EXPLAIN select key2 from cac.."><!-- hexo injector head_end start -->
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/katex@0.12.0/dist/katex.min.css">

<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/hexo-math@4.0.0/dist/style.css">
<!-- hexo injector head_end end --><meta name="generator" content="Hexo 5.4.1"><link rel="alternate" href="/blog/atom.xml" title="janedler's blog" type="application/atom+xml">
</head><body class="is-flex is-flex-direction-column"><header class="header-widget is-flex-shrink-0 is-hidden-mobile"><div class="container is-fullhd is-flex is-justify-content-space-between is-align-items-center is-full-height"><section class="is-hidden-mobile is-flex-shrink-0"><h2><a href="/blog/">田园牧歌(*︶*)'s blog</a></h2></section><h3 class="is-hidden-mobile is-family-serif is-full-height is-flex is-align-items-center is-flex-shrink-0"><div class="is-full-height" id="postTopic"><p class="is-full-height is-flex-shrink-0 is-flex is-align-items-center is-justify-content-center">【手撕SQL】SQLite UPDATE 命令执行计划</p><p class="is-full-height is-flex-shrink-0 is-flex is-align-items-center is-justify-content-center">点击返回顶部</p></div></h3><aside class="is-flex-shrink-0"><h3 class="is-inline-block"><a href="/blog/">首页</a></h3><h3 class="is-inline-block"><a href="/blog/about">关于</a></h3><h3 class="is-inline-block"><a href="/blog/archives">归档</a></h3></aside></div></header><header class="is-flex header-widget is-flex-shrink-0 is-align-items-center is-justify-content-center is-hidden-tablet"><h3 class="is-inline-block"><a href="/blog/">首页</a></h3><h3 class="is-inline-block"><a href="/blog/about">关于</a></h3><h3 class="is-inline-block"><a href="/blog/archives">归档</a></h3></header><main><main class="container is-max-widescreen content section post-page pt-4 px-4"><div class="columns is-flex-desktop is-justify-content-center is-flex-direction-row-reverse"><div class="column is-3 is-hidden-mobile"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E9%97%AE%E9%A2%98%E6%8A%9B%E5%87%BA"><span class="toc-text">一、问题抛出</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E5%88%86%E6%9E%90%E6%AD%A5%E9%AA%A4"><span class="toc-text">二、分析步骤</span></a></li><li class="toc-item toc-level-1"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%80%BB%E7%BB%93"><span class="toc-text">三、总结</span></a></li></ol></div><div class="column is-9"><header class="my-4"><a href="/blog/tags/Java"><i class="tag post-item-tag">Java</i></a><a href="/blog/tags/SQL"><i class="tag post-item-tag">SQL</i></a></header><h1 class="mt-0 mb-1 is-family-serif" id="postTitle">【手撕SQL】SQLite UPDATE 命令执行计划</h1><time class="has-text-grey" datetime="2022-09-22T16:00:00.000Z">2022-09-23</time><article class="mt-2 post-content"><p><img src="https://janedler.oss-cn-beijing.aliyuncs.com/images/sqlite.png" alt="sqlite"></p>
<p>本文主要讲述SQLite通过EXPLAIN命令获取UPDATE语句的执行计划。分析UPDATE执行过程中对表中各个属性的操作过程。</p>
<span id="more"></span>

<h1 id="一、问题抛出"><a href="#一、问题抛出" class="headerlink" title="一、问题抛出"></a>一、问题抛出</h1><p>表结构如下：</p>
<p><img src="https://janedler.oss-cn-beijing.aliyuncs.com/images/202209230929100.png" alt="image-20220923092917325"></p>
<p>data属性字段的类型为text，在实际中data字段保存着bigdata（测试过程时候发现有24M）。<br>写作本文的目的在于分析。通过下面update语句，SQLite在执行过程中是否会把data属性字段里面的数据读入到内存中。</p>
<pre><code class="mysql">update cache_object set key1 = &#39;theme_1&#39; where key1 = &#39;theme&#39;;
</code></pre>
<h1 id="二、分析步骤"><a href="#二、分析步骤" class="headerlink" title="二、分析步骤"></a>二、分析步骤</h1><p>1、先分析一条SELECT语句：通过key1=‘theme’去查询对应元组里面的属性key2</p>
<pre><code class="mysql">EXPLAIN select key2 from cache_object where key1 = &#39;theme&#39;;
</code></pre>
<p><img src="https://janedler.oss-cn-beijing.aliyuncs.com/images/202209230936202.png" alt="image-20220923093615168"><br>addr表示命令在寄存器中的地址编号。<br>opcode表示操作码；<br>p1、p2、p3、p4、p5表示寄存器；<br>comment表示对于属性字段的描述。</p>
<p><strong>下面对指令逐条进行分析</strong></p>
<p>**[0]  Init  **<br>p2为9，表示执行跳转到第9行开始。</p>
<p>**[9]  Transaction  **<br>当前SQL没有事务尚未激活；p1为启动事务的数据库文件的索引；p2为0表示启动读取事务，如果p2为1表示启动写入事务；</p>
<p><strong>[9]  Goto</strong><br>p2为1表示跳转到第1行。</p>
<p><strong>[1]  OpenRead</strong><br>p3(0：主数据库、1：临时表数据库、&gt;1：附加数据库)；p1表示光标标识符； p2表示打开了只读光标；p5表示此光标仅用于等式查找；p4(1、整数：表示打开的表必现是一个表b-tree；2、指向KeyInfo结构(P4_KeyInfo)的指针：表示打开的表必须是索引b_tree，其中KeyInfo Object定义该索引b_tree的内容和排序顺序)</p>
<p><strong>[2]  string8</strong><br>p4指向以nul结尾的UTF-8字符串;</p>
<p><strong>[3]  SeekGE</strong><br>光标开始移动查找;</p>
<p><strong>[4]  IdxGT</strong><br>以P3开头的P4寄存器值形成一个未打包的索引键，其中省略了PRIMARY键。将此键值与P1当前指向的索引进行比较，忽略末尾的PRIMARY key或ROWID字段。如果P1索引项大于键值，则跳到P2。否则跳到下一条指令。</p>
<p><strong>[5]  Column</strong><br>将光标P1指向的数据解释为使用MakeRecord指令构建的结构。从此记录中提取第P2列。如果记录中的值少于（P2+1），则提取NULL，提取的值存储在寄存器P3中；</p>
<p><strong>[6]  ResultRow</strong><br>p2表示查询了多少属性(列);</p>
<p><strong>[7]  Next</strong><br>循环查找，向前移动光标P1，使其指向其表或索引中的下一个键/数据对。如果没有更多的键/值对，则执行以下指令。但如果光标前进成功，请立即跳到P2;</p>
<p><strong>[8]  Halt</strong><br>立即退出。所有打开的光标等都会自动关闭。</p>
<p>2、通过SELECT语句分析的基础上，进行UPDATE分析</p>
<pre><code class="mysql">EXPLAIN update cache_object set key1 = &#39;theme_1&#39; where key1 = &#39;theme&#39;;
</code></pre>
<p><img src="https://janedler.oss-cn-beijing.aliyuncs.com/images/202209231109332.png" alt="image-20220923110922274"><br><img src="https://janedler.oss-cn-beijing.aliyuncs.com/images/202209231109274.png" alt="image-20220923110922275"> </p>
<p><strong>[3]  OpenEphemeral</strong><br>打开临时表的新光标P1。即使主数据库是只读的，光标也始终以读/写方式打开。游标关闭时，临时表将自动删除;</p>
<p><strong>[28]  SCopy</strong><br>将寄存器P1的浅拷贝复制到寄存器P2中。此指令生成值的浅拷贝。如果值是字符串或blob，则副本只是指向原始的指针，因此如果原始更改，副本也会更改;</p>
<p><strong>[33]  MakeRecord</strong><br>将以P1开头的P2寄存器转换为记录格式，用作数据库表中的数据记录或索引中的键。Column操作码可以稍后解码记录。</p>
<p><strong>[42]  Insert</strong><br>将一个条目写入光标P1的表中。如果新条目不存在或现有条目的数据被覆盖，则会创建一个新条目。</p>
<h1 id="三、总结"><a href="#三、总结" class="headerlink" title="三、总结"></a>三、总结</h1><p>通过EXPLAIN对UPDATE指令执行计划进行分析，当我们更新某个属性值的时候，SQLite先会创建一个临时表，临时表中的属性与原表中的数据进行了一个浅拷贝，但是对于BLOB类型的属性，只在指向了原始指针。</p>
<!--SQLite字节码引擎：https://www.sqlite.org/opcode.html-->



































</article><section class="jump-container is-flex is-justify-content-space-between my-6"><!-- em is empty placeholder--><a class="button is-default" href="/blog/2022/11/29/Spring/Spring%E8%8E%B7%E5%8F%96Bean/" title="【Spring源码解析系列】 Spring获取Bean"><i class="iconfont icon-prev mr-2 has-text-grey"></i><span class="has-text-weight-semibold">上一页: 【Spring源码解析系列】 Spring获取Bean</span></a><a class="button is-default" href="/blog/2021/03/12/%E6%89%8B%E6%92%95Ngnix/%E7%BD%91%E7%AB%99%E8%B7%A8%E5%9F%9F%E9%97%AE%E9%A2%98/" title="【手撕Ngnix】网站跨域问题"><span class="has-text-weight-semibold">下一页: 【手撕Ngnix】网站跨域问题</span><i class="iconfont icon-next ml-2 has-text-grey"></i></a></section><article class="mt-6 comment-container"><script async repo="janedler/blog" src="https://utteranc.es/client.js" issue-term="pathname" theme="preferred-color-scheme"></script></article></div></div></main></main><footer class="is-flex is-flex-direction-column is-align-items-center is-flex-shrink-0 is-family-serif"><section class="sns-container"><!-- Github--><a title="github" target="_blank" rel="noopener nofollow" href="//github.com//janedler"><i class="iconfont icon-github"></i></a><!-- Ins--><!-- RSS--><a title="rss" target="_blank" rel="noopener nofollow" href="/blog/atom.xml"><i class="iconfont icon-rss"></i></a><!-- 知乎--><!-- 领英--><!-- 脸书--></section><p><span>Copyright ©</span><span> 田园牧歌(*︶*) 2022</span></p><div class="is-flex is-justify-content-center is-flex-wrap-wrap"><p>Powered by Hexo &verbar;&nbsp;</p><p class="is-flex is-justify-content-center"><a title="Hexo theme author" target="_blank" rel="noopener" href="//github.com/haojen">Theme by Haojen&nbsp;</a></p><div style="margin-top: 2px"><a class="github-button" title="github-button" target="_blank" rel="noopener" href="https://github.com/haojen/hexo-theme-Claudia" data-color-scheme="no-preference: light; light: light; dark: dark;" data-show-count="true"></a></div></div><div><span></span></div></footer><script async defer src="https://buttons.github.io/buttons.js"></script><script src="/blog/js/post.js"></script></body></html>